package cn.edu.ljl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//用于连接数据库

public class DBMS {
    private static String DB_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String DB_URL    = "jdbc:mysql://localhost:3306/grade";
    private static String DB_USER   = "root";
    private static String DB_PASS   = "123456";

    public static void insert(String sql) throws SQLException {
        excute(sql);
    }

    public static void delete(String sql) throws SQLException {
        excute(sql);
    }

    public static void update(String sql) throws SQLException {
        excute(sql);
    }

    private static void excute(String sql) throws SQLException {
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        Statement statement = connection.createStatement();
        statement.execute(sql);
    }

    public static List<Student> getStudents(String sql) throws SQLException {
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        List<Student> students = new ArrayList<Student>();
        Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {

            Student student = new Student();

            student.setID(rs.getInt("id"));
            student.setNumber(rs.getInt("numbb"));
            student.setName(rs.getString("nam"));
            student.setSubject(rs.getString("subje"));
            student.setScore(rs.getInt("score"));
            student.setFeel(rs.getString("feel"));
            student.setPic(rs.getString("pic"));

            students.add(student);
        }
        return students;
    }

    public static List<Experience> getExperience(String sql) throws SQLException {
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        List<Experience> experiences = new ArrayList<Experience>();
        Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        Statement statement = connection.createStatement();
        ResultSet resultSets = statement.executeQuery(sql);

        while (resultSets.next()) {

            Experience experience = new Experience();

            experience.setID(resultSets.getInt("id"));
            experience.setName(resultSets.getString("nam"));
            experience.setCourse(resultSets.getString("course"));
            experience.setAdvice(resultSets.getString("advice"));
            experiences.add(experience);
        }
        return experiences;
    }

    public static List<file> getFiles(String sql) throws SQLException {
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        List<file> files = new ArrayList<file>();
        Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {

            file file = new file();

            file.setID(rs.getInt("id"));
            file.setName(rs.getString("nam"));
            file.setFile(rs.getString("file"));

            files.add(file);
        }
        return files;
    }


    public static List<sign> getSigns(String sql) throws SQLException {
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        List<sign> signs = new ArrayList<sign>();
        Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {

            sign s = new sign();

            s.setID(rs.getInt("id"));
            s.setName(rs.getString("nam"));
            s.setPassword(rs.getString("password"));

            signs.add(s);
        }
        return signs;
    }




}
